import math
import time
from datetime import datetime, timedelta
import asyncio
import threading
from sqlalchemy import func, cast, Integer, desc
from Common import Common
import requests
from bilibili_api import search, sync, video_zone
from sqlalchemy import func

from script.muti_thread import execute_async_task_concurrently
from utils.DBUtil import DBUtil
from models import MonitoringTask, MonitoredVideo, MonitoredVideoHistory, VideoZone, VideoZoneHistory, Alert
from script.spider_common_method import save_video, check_task_not_in, delete_video_comments, \
    get_all_comment, check_video_can_in
from utils.SchedulerUtil import SchedulerUtil


def new_task():
    scheduler = SchedulerUtil.get_scheduler()
    if scheduler.get_job('update_comment'):
        scheduler.pause_job('update_comment')
        print("运行new_task,暂停update_comment")
        asyncio.run(execute_new_task())
        print("new_task运行完毕,恢复update_comment")
        scheduler.resume_job('update_comment')
    else:
        asyncio.run(execute_new_task())


# 从十个热搜话题更新任务
async def execute_new_task():
    # 先初始化,如果没初始化zone的话
    init_video_zone()
    # 删除一天内没在增长的视频 和 爬取不到评论的视频
    zone_knockout()
    # 更新记录
    record_zone_change()
    # 更新当前的网暴率
    statistics_video_zone()
    # 更新每个zone需要爬取的视频
    update_should_count()
    # 执行更新
    update_monitoring_task_in_zone()
    if Common.AUTO_NEW_TASK_FORM_HOT:
        add_hot_topic()


def init_video_zone():
    session = DBUtil.get_thread_session()
    table_content = session.query(VideoZone).first()
    # 初始化，代表数据库里啥也没有
    if not table_content:
        print('初始化zone')
        # 此时应该把updat_task停止
        zones = video_zone.get_zone_list()
        # 先将 zones 中的 tid 值去重
        unique_tids = set()
        unique_zones = []
        for z in zones:
            if z.get('tid'):
                tid = z.get('tid')
                if tid not in unique_tids:
                    unique_tids.add(tid)
                    unique_zones.append(z)

        # 然后处理 unique_zones
        for z in unique_zones:
            tname = z.get('name')
            tid = z.get('tid')
            if tid == 202 or tid == 203 or tid == 204 or tid == 205 or tid == 206:
                continue

            # 创建新的 VideoZone 对象并添加到 session
            v = VideoZone(tname=tname, tid=tid)
            session.add(v)

        # 提交事务，将数据保存到数据库
        session.commit()
        # 第一次每个类别爬取n个视频
        init_monitoring_task_in_zone()


# 多线程执行这两个任务,需要输入函数逻辑
def init_monitoring_task_in_zone():
    execute_async_task_concurrently(items=DBUtil.get_thread_session().query(VideoZone).all(),
                                    task_function=init_monitoring_task_for_zone)


def update_monitoring_task_in_zone():
    execute_async_task_concurrently(items=DBUtil.get_thread_session().query(VideoZone).all(),
                                    task_function=update_monitoring_task_for_zone)


async def get_videos_with_retry(tid, day):
    max_retries = 10  # 最大重试次数
    retries = 0

    while retries < max_retries:
        try:
            # 尝试获取评论
            videos = await video_zone.get_zone_top10(tid=tid, day=day)

            # 如果成功获取评论，则直接返回评论
            return videos
        except Exception as e:
            # 捕获异常并处理
            error_message = str(e)  # 将异常信息转换为字符串并保存
            # logging.error(f'{bvid}报错{error_message}')
            retries += 1
            print(f'{tid}重试第{retries}次...')
            time.sleep(1)
    # 如果超过最大重试次数仍无法成功，则输出错误信息并返回 None
    print(f'{tid}无法连接服务器，超过最大重试次数')
    return None


async def init_monitoring_task_for_zone(zone):
    limit = Common.ZONE_INITIAL_VALUE
    session = DBUtil.get_thread_session()
    print(f'在 {threading.current_thread().name} 中初始化 zone {zone.tid}，爬取 {limit} 个视频并获取评论')
    selected_videos = []
    videos = []
    try:
        videos = await get_videos_with_retry(tid=zone.tid, day=7)
    except Exception as e:
        print(f'{e}{zone}retry报错')
    count = 0
    for v in videos:
        if check_video_can_in(v['bvid']):
            error_message = await save_video(v['bvid'])
            if error_message:
                continue
            error_message = await get_all_comment(v['bvid'])
            if error_message:
                monitored_video = session.query(MonitoredVideo).filter_by(bvid=v['bvid']).first()
                if monitored_video:
                    session.delete(monitored_video)
                    session.commit()
                continue
            count += 1
            selected_videos.append(v['bvid'])
            if count >= limit:
                break
    task = MonitoringTask(task_name=zone.tname, task_type='zone', topic=str(zone.tid),
                          monitored_videos=','.join(selected_videos))
    session.add(task)
    session.commit()
    print(f"zone {zone.tid} 初始化完成")


async def update_monitoring_task_for_zone(zone):
    should_count = zone.t_should_count
    now_count = zone.t_count
    if should_count < now_count:
        delete_zone_video(zone.tname, zone.tid, now_count - should_count)
        print(f"{zone.tname} 视频减少 {now_count - should_count} 个")
    elif should_count > now_count:
        await add_zone_video(zone.tname, zone.tid, should_count - now_count)
        print(f"{zone.tname} 视频增加 {should_count - now_count} 个")


# 减少这个任务的视频量
# 每个视频都有两条以上历史数据则可以删除评论暴力占比增加缓慢的几条
# 否则删除目前评论暴力占比低的几条
def delete_zone_video(task_name, tid, del_count):
    session = DBUtil.get_thread_session()
    task = session.query(MonitoringTask).filter_by(task_name=task_name, topic=str(tid)).first()
    selected_videos = task.monitored_videos.split(',')
    if all(
            session.query(
                MonitoredVideoHistory.bvid,
                func.count(MonitoredVideoHistory.id)
            )
                    .filter(MonitoredVideoHistory.bvid.in_(selected_videos))
                    .group_by(MonitoredVideoHistory.bvid)
                    .having(func.count(MonitoredVideoHistory.id) >= 2)
    ):
        # 将 datetime 字段转换为 UNIX 时间戳
        record_time_cast = cast(MonitoredVideoHistory.record_time, Integer).label('record_time_ts')

        # 获取最近两条历史记录，并按照时间戳的降序排序
        history_query = session.query(MonitoredVideoHistory).filter(
            MonitoredVideoHistory.bvid.in_(selected_videos)).order_by(record_time_cast.desc()).limit(
            2).subquery()

        # 计算每个 bvid 的 did_violence_count 增长量
        if Common.ZONE_UPDATE_BY == 0:
            diff_query = (
                session.query(
                    MonitoredVideoHistory.bvid,
                    func.max(MonitoredVideoHistory.violence_count).label('max_count'),
                    func.min(MonitoredVideoHistory.violence_count).label('min_count')
                )
                    .join(history_query, MonitoredVideoHistory.id == history_query.c.id)
                    .group_by(MonitoredVideoHistory.bvid)
                    .having(func.count(MonitoredVideoHistory.id) >= 2)
                    .subquery()
            )
        elif Common.ZONE_UPDATE_BY == 1:
            diff_query = (
                session.query(
                    MonitoredVideoHistory.bvid,
                    func.max(MonitoredVideoHistory.sarcasm_count).label('max_count'),
                    func.min(MonitoredVideoHistory.sarcasm_count).label('min_count')
                )
                    .join(history_query, MonitoredVideoHistory.id == history_query.c.id)
                    .group_by(MonitoredVideoHistory.bvid)
                    .having(func.count(MonitoredVideoHistory.id) >= 2)
                    .subquery()
            )
        else:
            diff_query = (
                session.query(
                    MonitoredVideoHistory.bvid,
                    func.max(MonitoredVideoHistory.did_violence_count).label('max_count'),
                    func.min(MonitoredVideoHistory.did_violence_count).label('min_count')
                )
                    .join(history_query, MonitoredVideoHistory.id == history_query.c.id)
                    .group_by(MonitoredVideoHistory.bvid)
                    .having(func.count(MonitoredVideoHistory.id) >= 2)
                    .subquery()
            )
        # 查询增长量最低的 count 个 bvid
        result = (
            session.query(diff_query.c.bvid)
                .order_by(diff_query.c.max_count - diff_query.c.min_count)
                .limit(del_count)
                .all()
        )

        # 获取 bvid 列表
        bvid_list = [row[0] for row in result]

        for bvid in bvid_list:
            selected_videos.remove(bvid)
            session.delete(session.query(MonitoredVideo).filter_by(bvid=bvid).first())
            delete_video_comments(bvid)
        task.monitored_videos = ','.join(selected_videos)
        session.commit()
    else:
        # 按照 did_violence_count 和 comment_count 的商升序排序，并限制返回数量为 count 条
        if Common.ZONE_UPDATE_BY == 0:
            videos_to_delete = (
                session.query(MonitoredVideo)
                    .order_by(MonitoredVideo.violence_count / MonitoredVideo.comment_count)
                    .limit(del_count)
                    .all()
            )
        elif Common.ZONE_UPDATE_BY == 1:
            videos_to_delete = (
                session.query(MonitoredVideo)
                    .order_by(MonitoredVideo.sarcasm_count / MonitoredVideo.comment_count)
                    .limit(del_count)
                    .all()
            )
        else:
            videos_to_delete = (
                session.query(MonitoredVideo)
                    .order_by(MonitoredVideo.did_violence_count / MonitoredVideo.comment_count)
                    .limit(del_count)
                    .all()
            )
        del_bvid = []
        # 删除查询到的数据
        for video in videos_to_delete:
            del_bvid.append(video.bvid)
            session.delete(video)
        for bvid in del_bvid:
            selected_videos.remove(bvid)
            delete_video_comments(bvid)
        task.monitored_videos = ','.join(selected_videos)
        session.commit()


# 增加这个任务的视频量
async def add_zone_video(task_name, tid, add_count):
    session = DBUtil.get_thread_session()
    task = session.query(MonitoringTask).filter_by(task_name=task_name, task_type='zone').first()
    selected_videos = task.monitored_videos.split(',')
    videos = await video_zone.get_zone_top10(tid=tid, day=3)
    count = 0
    for v in videos:
        if check_video_can_in(v['bvid']):
            error_message = await save_video(v['bvid'])
            if error_message:
                continue
            error_message = await get_all_comment(v['bvid'])
            if error_message:
                monitored_video = session.query(MonitoredVideo).filter_by(bvid=v['bvid']).first()
                if monitored_video:
                    session.delete(monitored_video)
            count += 1
            selected_videos.append(v['bvid'])
            if count >= add_count:
                break
    task.monitored_videos = ','.join(selected_videos)
    session.commit()


# 更新video_zone表中的t_should_count的函数
def update_should_count():
    print("更新每个zone需要爬取的视频量")
    session = DBUtil.get_thread_session()
    # 二八分配,前百分之20享受百分之80的资源
    # 从数据库中检索所有的 zones，并按照 策略 降序排序
    if Common.ZONE_UPDATE_BY == 0:
        zones = session.query(VideoZone).order_by(desc(VideoZone.average_violence_rate)).all()
    elif Common.ZONE_UPDATE_BY == 1:
        zones = session.query(VideoZone).order_by(desc(VideoZone.average_sarcasm_rate)).all()
    else:
        zones = session.query(VideoZone).order_by(desc(VideoZone.average_did_violence_rate)).all()
    total_zones = len(zones)
    all_count = Common.ZONE_ALL_VIDEO_COUNT
    remain_count = Common.ZONE_ALL_VIDEO_COUNT
    min_count = Common.ZONE_MIN_VALUE

    # 前百分之20的zone的数量
    top_zone = math.floor(total_zones * 0.2)
    # 后百分之80的zone的数量
    bottom_zone = total_zones - top_zone
    # 检查资源是否够二八分
    if all_count >= bottom_zone * min_count * 5:
        bottom_zone_every_count = math.floor(all_count * 0.2 / bottom_zone)
        top_zone_every_count = math.floor((all_count - bottom_zone_every_count) / top_zone)
        for i, zone in enumerate(zones):
            if i <= top_zone:
                zone.t_should_count = top_zone_every_count
                remain_count -= top_zone_every_count
            else:
                zone.t_should_count = bottom_zone_every_count
                remain_count -= bottom_zone_every_count
        for i, zone in enumerate(zones):
            if i <= top_zone and remain_count > 0:
                zone.t_should_count += 1
                remain_count -= 1
    # 不满足则把所有的zone满足最低要求ZONE_MIN_VALUE,多余的平均分给前百分之20
    else:
        # 满足每个zone的should_count的最低要求
        for zone in zones:
            zone.t_should_count = min_count
            remain_count -= min_count
        # 剩下的分给前百分之20
        for i, zone in enumerate(zones):
            if i < top_zone and remain_count > 0:
                zone.t_should_count += 1
                remain_count -= 1
    # 提交更改
    session.commit()


# 统计video_zone
def statistics_video_zone():
    session = DBUtil.get_thread_session()
    print(str(datetime.now()) + '开始更新zone表')
    video_zone = session.query(VideoZone).all()
    for z in video_zone:
        monitor_task = session.query(MonitoringTask).filter_by(topic=str(z.tid), task_type='zone').first()
        if not monitor_task:
            print("zone的任务还没初始化")
            return
        if monitor_task.monitored_videos == '':
            z.average_violence_rate = 0
            z.average_sarcasm_rate = 0
            z.average_did_violence_rate = 0
            z.t_count = 0
            continue
        bvids = monitor_task.monitored_videos.split(',')
        average_violence_rate_sum = 0
        average_sarcasm_rate_sum = 0
        average_did_violence_rate = 0
        no_comment_count = 0
        for bvid in bvids:
            monitor_video = session.query(MonitoredVideo).filter_by(bvid=bvid).first()
            if monitor_video.comment_count == 0 or monitor_video.last_comment_time is None:
                no_comment_count += 1
                continue
            average_violence_rate_sum += monitor_video.violence_count / monitor_video.comment_count
            average_sarcasm_rate_sum += monitor_video.sarcasm_count / monitor_video.comment_count
            average_did_violence_rate += monitor_video.did_violence_count / monitor_video.comment_count
        z.average_violence_rate = average_violence_rate_sum / (len(bvids) - no_comment_count)
        z.average_sarcasm_rate = average_sarcasm_rate_sum / (len(bvids) - no_comment_count)
        z.average_did_violence_rate = average_did_violence_rate / (len(bvids) - no_comment_count)
        z.t_count = len(bvids)
    session.commit()
    print(str(datetime.now()) + 'zone表更新结束')


def record_zone_change():
    session = DBUtil.get_thread_session()
    video_zones = session.query(VideoZone).all()
    for zone in video_zones:
        # 创建一个 VideoZoneHistory 对象,并填充数据
        video_zone_history = VideoZoneHistory(
            tid=zone.tid,
            tname=zone.tname,
            average_violence_rate=zone.average_violence_rate,
            average_sarcasm_rate=zone.average_sarcasm_rate,
            average_did_violence_rate=zone.average_did_violence_rate,
            t_count=zone.t_count,
            t_should_count=zone.t_should_count,
            record_time=datetime.now()
        )
        # 添加到 session 并提交
        session.add(video_zone_history)
    session.commit()


# 只淘汰网暴率不增长的视频,不增加,因为网暴率增长了自己会增加爬的视频
def zone_knockout():
    print("开始淘汰网暴率下降的视频")
    session = DBUtil.get_thread_session()
    tasks = session.query(MonitoringTask).filter_by(task_type='zone').all()
    alerts = session.query(Alert).all()
    alert_bvids = []
    # 已经报警网暴的视频不删除
    if alerts:
        for a in alerts:
            alert_bvids.append(a.video_bvid)

    for task in tasks:
        if task.monitored_videos == '':
            continue
        video_bvids = task.monitored_videos.split(',')
        for bvid in video_bvids:
            if bvid in alert_bvids:
                continue
            if session.query(MonitoredVideo).filter_by(bvid=bvid).first() is None or session.query(
                    MonitoredVideo).filter_by(bvid=bvid).first().last_comment_time is None:
                delete_video_comments(bvid)
                video_bvids.remove(bvid)
                print(f"爬取不到评论,淘汰{bvid}")
            # 删除一天以上网暴评论数量下降的视频
            closest_record = session.query(MonitoredVideoHistory).filter_by(bvid=bvid).order_by(
                func.abs(MonitoredVideoHistory.record_time - datetime.now())).first()
            if closest_record is None:
                continue
            one_day_ago = datetime.now() - timedelta(days=Common.ZONE_KNOCKOUT_INTERVAL)
            one_day_ago_record = session.query(MonitoredVideoHistory).filter(
                MonitoredVideoHistory.bvid == bvid,
                MonitoredVideoHistory.record_time < closest_record.record_time,
                MonitoredVideoHistory.record_time >= one_day_ago).first()
            if one_day_ago_record is None:
                continue
            # 没有超过一天则删除网暴概率最小的
            else:
                if closest_record.violence_count / closest_record.comment_count <= one_day_ago_record.violence_count / one_day_ago_record.comment_count:
                    delete_video_comments(bvid)
                    video_bvids.remove(bvid)
                    print(f"淘汰{bvid}")
        task.monitored_videos = ','.join(video_bvids)
    session.commit()


def add_hot_topic():
    response = {}
    url = 'https://api.bilibili.com/x/web-interface/wbi/search/square?limit=10'
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/'
                      '58.0.3029.110 Safari/537.3'
    }
    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        data = response.json()
        need_add_task = []
        for i in data['data']['trending']['list']:
            key = i['keyword']
            if check_task_not_in(key):
                need_add_task.append(key)
        execute_async_task_concurrently(items=need_add_task, task_function=save_task)


# 存储新建的任务，每个话题存三个，只存播放量高于10000的
async def save_task(key):
    session = DBUtil.get_thread_session()
    data = await search.search(key)
    videos = []
    for i in data.get('result'):
        if i.get('result_type') == 'video':
            videos = i.get('data')
    need_add_bvid = []
    for v in videos:
        if int(v['play']) > 10000:
            if check_video_can_in(v['bvid']):
                need_add_bvid.append(v['bvid'])
    selected_videos = []
    count = 0
    for bvid in need_add_bvid:
        error_message = await save_video(bvid)
        if error_message:
            continue
        error_message = await get_all_comment(bvid)
        if error_message:
            monitored_video = session.query(MonitoredVideo).filter_by(bvid=bvid).first()
            if monitored_video:
                session.delete(monitored_video)
            continue
        selected_videos.append(bvid)
        count += 1
        if count >= Common.HOT_INITIAL_VALUE:
            break
    task = MonitoringTask(task_name=key, task_type='热搜话题', topic=key,
                          monitored_videos=','.join(selected_videos), is_active=True)
    # 将监控话题任务数据存储到数据库
    session.add(task)
    session.commit()

#
# async def init_monitoring_task_in_zone():
#     limit = Common.ZONE_INITIAL_VALUE
#     session = DBUtil.get_thread_session()
#     print(f'初始化时每个zone爬取{limit}个视频,并爬取评论')
#     for z in session.query(VideoZone).all():
#         selected_videos = []
#         # print("建立任务" + str(z.tid))
#         videos = await video_zone.get_zone_top10(tid=z.tid, day=7)
#         count = 0
#         for v in videos:
#             if check_video_can_in(v['bvid']):
#                 await save_video(v['bvid'])
#                 error_message = await get_all_comment(v['bvid'])
#                 if error_message:
#                     monitored_video = session.query(MonitoredVideo).filter_by(bvid=v['bvid']).first()
#                     if monitored_video:
#                         session.delete(monitored_video)
#                         session.commit()
#                     continue
#                 count += 1
#                 selected_videos.append(v['bvid'])
#                 if count >= limit:
#                     break
#         task = MonitoringTask(task_name=z.tname, task_type='zone', topic=str(z.tid),
#                               monitored_videos=','.join(selected_videos))
#         session.add(task)
#     session.commit()
#     print("初始化zone任务完成")q
#
# async def update_monitoring_task_in_zone():
#     session = DBUtil.get_thread_session()
#     print('开始执行更新每个zone的视频量')
#     for v in session.query(VideoZone).all():
#         should_count = v.t_should_count
#         now_count = v.t_count
#         if should_count < now_count:
#             delete_zone_video(v.tname, v.tid, now_count - should_count)
#             print(f"{v.tname}视频减少{now_count - should_count}个")
#         elif should_count > now_count:
#             await add_zone_video(v.tname, v.tid, should_count - now_count)
#             print(f"{v.tname}视频增加{should_count - now_count}个")
# statistics_video_zone()
# add_hot_topic()
# sync(save_task("周鸿祎回应买家没付清钱"))
